from check_privilege import *
from DataAccess.DBConnFactory import DBConnFactory
from Misc.xlDate import read_xl_date
from Config.ImportConfig import *

from datetime import datetime
import win32com.client 
import os.path


def add_cash_adj(ref_date, file_path = None):
	if not file_path:
		file_path = CASH_ADJ_FILE_PATH
	
	if(not os.path.exists(file_path)):
		print 'error, cash adjust record file NOT EXIST.'
		return 
			
	xApp = win32com.client.Dispatch("Excel.Application")
	xBook = xApp.Workbooks.Open(file_path)
	xSht = xBook.Worksheets(1)
	down_rows = xSht.Range("A65536").End(win32com.client.constants.xlUp).Row + 1

	conn = DBConnFactory().get_db_connection('PKEDB')
	sql_text = '''merge into cash_adj adj
	            using (select TO_DATE(:1,'yyyy-mm-dd') as ref_date,
							  :2 as portfolio_id,							  
							  :3 as amount,							  
							  :4 as curncy,
							  :5 as adj_type,					
							  :6 as detail
					    from dual) t
				on (adj.ref_date=t.ref_date and adj.portfolio_id=t.portfolio_id and adj.adj_type=t.adj_type)
				when matched then update set
					adj.amount = t.amount,
					adj.currency = t.curncy,
					adj.detail = t.detail
				when not matched then insert (ref_date,portfolio_id,amount,currency,adj_type,detail)
					values(t.ref_date,t.portfolio_id,t.amount,t.curncy,t.adj_type,t.detail)'''

	for i in range(2, down_rows):
		
		d = read_xl_date(float(xSht.Cells(i, 1).Value))
		if d < ref_date:
			continue
		elif d > ref_date:
			return 
		else:
			portf = xSht.Cells(i, 2).Value
			adjtype = xSht.Cells(i, 3).Value
			amount = float(xSht.Cells(i, 4).Value)
			curncy = xSht.Cells(i, 5).Value
			detail = xSht.Cells(i, 6).Value		
			
			conn.cursor().execute(sql_text, 
							(d.strftime('%Y-%m-%d'),portf,amount,curncy,adjtype,detail))
			conn.commit()	
	
	
	xBook.Close(SaveChanges = 0)
	del xApp
	print 'info, add cash adjust completed...'
	print ' '
	
	
if __name__ == '__main__':
	if not check_usr_privilege():
		print 'Wrong User or Password...'
		exit(0)
	
	date_str = raw_input('reference date(yyyymmdd): ')
	if len(date_str)!=8:
		print 'ERROR: NOT Required Date Format.'	
		exit(0)
		
	ref_date = datetime.strptime(date_str, '%Y%m%d').date()
	add_cash_adj(ref_date)	
	
	